﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using DAO;
using DTO;

namespace QLHocSinh
{
    public partial class frmTongKet : Form
    {
        public frmTongKet()
        {
            InitializeComponent();
        }

        private void load_nienhoc()
        {
            for (int i = 2000; i < 2020; i++)
            {
                // String namhoc = i + " - " + (i + 1);
                cbb_nienhoc.Items.Add(i);
            }
            int namht = DateTime.Now.Year;
            // String nam = namht + " - " + (namht + 1);
            cbb_nienhoc.Text = namht.ToString();
        }

        private void load_hocky()
        {
            string sSQL = "Select id_hk, ten_hk from hoc_ky";
            DataTable dt = DAO.KetNoi.clsCacHamChung.select_dulieu(sSQL);
            cbb_hocky.DataSource = dt;
            cbb_hocky.DisplayMember = "ten_hk";
            cbb_hocky.ValueMember = "id_hk";
        }

        private void load_khoi()
        {
            string sSQL = "Select id_khoi, ten_khoi  from khoi_lop";
            DataTable dt = DAO.KetNoi.clsCacHamChung.select_dulieu(sSQL);
            cbb_khoi.DataSource = dt;
            cbb_khoi.DisplayMember = "ten_khoi";
            cbb_khoi.ValueMember = "id_khoi";
        }

        private void load_lop()
        {
            string sSQL = "Select id_lop, ten_lop  from Lop where nam_hoc = " + cbb_nienhoc.Text + " and id_khoi = " + cbb_khoi.SelectedValue;
            DataTable dt = DAO.KetNoi.clsCacHamChung.select_dulieu(sSQL);
            cbb_lop.DataSource = dt;
            cbb_lop.DisplayMember = "ten_lop";
            cbb_lop.ValueMember = "id_lop";
            if (cbb_lop.Items.Count > 0)
            {
                cbb_lop.SelectedIndex = 0;
            }
            else
            {
                cbb_lop.Text = "";
            }
        }

        private void load_hocluc()
        {
            string sSQL = "select id_hl, ten_hl from hoc_luc";
            DataTable dt = DAO.KetNoi.clsCacHamChung.select_dulieu(sSQL);
            cbb_hl.DataSource = dt;
            cbb_hl.DisplayMember = "ten_hl";
            cbb_hl.ValueMember = "id_hl";
            
        }

        private void load_hanhkiem()
        {
            string sSQL = "select id_h_kiem, ten_h_kiem from hanh_kiem";
            DataTable dt = DAO.KetNoi.clsCacHamChung.select_dulieu(sSQL);
            cbb_hk.DataSource = dt;
            cbb_hk.DisplayMember = "ten_h_kiem";
            cbb_hk.ValueMember = "id_h_kiem";
        }

        private void frmTongKet_Load(object sender, EventArgs e)
        {
            Cursor.Current = Cursors.WaitCursor;
            dgv_xeploai.AutoGenerateColumns = false;
            try
            {
                load_nienhoc();
                load_hocky();
                load_khoi();
                load_hocluc();
                load_hanhkiem();
                dgv_xeploai.Height = 510;
            }
            catch (Exception ex)
            {
                MessageBox.Show("Lỗi load dữ liệu " + ex, "Error");
            }
            Cursor.Current = Cursors.Default;
        }

        private void cbb_khoi_SelectedValueChanged(object sender, EventArgs e)
        {
            Cursor.Current = Cursors.WaitCursor;
            try
            {
                load_lop();
                // cbb_lop.SelectedIndex = -1;
            }
            catch (Exception ex)
            {
                MessageBox.Show("Lỗi load dữ liệu lớp " + ex, "Error");
            }
            Cursor.Current = Cursors.Default;
        }

        private void dgv_xeploai_RowPrePaint(object sender, DataGridViewRowPrePaintEventArgs e)
        {
            dgv_xeploai.Rows[e.RowIndex].Cells["Column1"].Value = e.RowIndex + 1;
        }

        private void load_mon(int mahs)
        {
            try
            {
                String sSQL = "select mh.ten_mh as [Tên môn học],dm.d_mieng_1 as DM1, dm.d_mieng_2 as DM2, dm.d_mieng_3 as DM3, dm.d_15_phut_1 as D15P1, dm.d_15_phut_2 as D15P2, dm.d_15_phut_3 as D15P3, dm.d_1_tiet_1 as D1T1, dm.d_1_tiet_2 as D1T2, dm.d_1_tiet_3 as D1T3, dm.d_thi as DT ";
                sSQL += " from mon_hoc mh, diem_mon dm ";
                sSQL += " where mh.id_mh = dm.id_mh and dm.id_lop = " + cbb_lop.SelectedValue + " and dm.id_hk = " + cbb_hocky.SelectedValue + " and dm.id_hs = " + mahs;
                DataTable dt = DAO.KetNoi.clsConnect.executeNonQueryTable(sSQL);
                dgv_chitiet.DataSource = dt;
            }
            catch (Exception ex)
            {
                MessageBox.Show("Lỗi hiện thi chi tiết môn học " + ex.Message, "Error");
            }
            
        }
        /*
         * Store thuc thi lay diem cua hoc sinh
        create proc Diem_TongKet(@malop int, @hk int)
as
begin
declare @mahs int
create table #hocsinh(
id_hs int,
dtbk1 float,
dtbk2 float,
dtbcn float, 
id_lop int,
id_hk int
)
Declare MangHS cursor for select id_hs from hs_lop where id_lop = @malop
open MangHS
Fetch next from MangHS into @mahs
	while(@@fetch_status=0)
	begin
	--
	declare @dtb float
	declare @diem float = 0
	declare @somon int = 0
	declare @dtbhk1 float = 0
	declare @dtbhk2 float = 0
	declare @dtbcn float = 0
	Declare MangDiem cursor For select dtb
							from diem_mon where id_lop = @malop and id_hk = @hk and id_hs = @mahs
	open MangDiem
	Fetch next from MangDiem into @dtb
	while(@@fetch_status=0)
	begin
		set @diem = @diem + @dtb
		set @somon = @somon + 1
		Fetch next from MangDiem into @dtb
	End
	if (@hk = 1)
	begin
		if (@somon = 0)
		begin
			set @dtbhk1 = 0
		end
		else
		begin
			set @dtbhk1 = round(@diem / @somon, 2)
		end
	end
	------
	else
	begin
		if (@somon = 0)
		begin
			set @dtbhk2 = 0
		end
		else
		begin
			set @dtbhk2 = round(@diem / @somon, 2)
		end
	--Lay diem trung binh hoc ky 1
	set @diem = 0
	set @somon = 0
	Declare MangHK1 cursor For select dtb
							from diem_mon where id_lop = @malop and id_hk = 1 and id_hs = @mahs
			open MangHK1
	Fetch next from MangHK1 into @dtb
	while(@@fetch_status=0)
	begin
		set @diem = @diem + @dtb
		set @somon = @somon + 1
		Fetch next from MangHK1 into @dtb
	End	
	if (@somon = 0)
	begin
		set @dtbhk1 = 0
	end
	else
	begin
		set @dtbhk1 = round(@diem / @somon, 2)
	end
	set @dtbcn = round((@dtbhk1 + @dtbhk2)/ 2, 2)		
	--
	close MangHK1
	Deallocate MangHK1
	end
	close MangDiem
	Deallocate MangDiem
	insert into #hocsinh values(@mahs, @dtbhk1, @dtbhk2,@dtbcn,@malop, @hk)
	--
	Fetch next from MangHS into @mahs
	end
	close MangHS
	Deallocate MangHS
	select hs.id_hs  as [Mã học sinh], hs.ho_hs + ' ' + hs.ten_hs as [Tên học sinh], 
			dm.dtbk1 as DTB1, dm.dtbk2 as DTB2, dm.dtbcn as DTB
		from hoc_sinh hs, #hocsinh dm where hs.id_hs = dm.id_hs and dm.id_lop = @malop and dm.id_hk = @hk 
		------
	drop table #hocsinh
end
         */
        private void load_xeploai()
        {
            try
            {
                String sSQL = "";
                sSQL = "select hs.id_hs as [Mã học sinh], hs.ho_hs + ' ' + hs.ten_hs as [Tên học sinh], tbhk1 as DTB1, tbhk2 as DTB2, tbhk as [DTB],  id_hl, ten_hl as [id_hl], id_h_kiem,  ten_h_kiem as [id_h_kiem] from diem_hk hk, hoc_sinh hs, hanh_kiem h, hoc_luc hl where hk.hoc_luc = hl.id_hl and hk.hanh_kiem = h.id_h_kiem and  hk.id_hs = hs.id_hs and hk.id_lop = " + cbb_lop.SelectedValue;
                DataTable dt = new DataTable();
                dt = DAO.KetNoi.clsConnect.executeNonQueryTable(sSQL);
                if (dt.Rows.Count <= 0)
                {
                    sSQL = "Exec Diem_TongKet " + cbb_lop.SelectedValue + ", " + cbb_hocky.SelectedValue;
                    dt = DAO.KetNoi.clsConnect.executeNonQueryTable(sSQL);
                }
                dgv_xeploai.DataSource = dt;
            }
            catch (Exception ex)
            {
                MessageBox.Show("Lỗi hiện thi chi tiết môn học " + ex.Message, "Error");
            }
        }
        private void checkBox1_Click(object sender, EventArgs e)
        {
            if (checkBox1.Checked)
            {
                dgv_chitiet.Visible = true;
                label5.Visible = true;
                dgv_xeploai.Height = 268;
                if (dgv_xeploai.RowCount > 0)
                {
                    int mahs = int.Parse(dgv_xeploai.Rows[0].Cells[3].Value.ToString());
                    load_mon(mahs);
                }
                dong = -1;
            }
            else
            {
                dgv_chitiet.Visible = false;
                label5.Visible = false;
                dgv_xeploai.Height = 510;
            }
        }

        private void dgv_chitiet_RowPrePaint(object sender, DataGridViewRowPrePaintEventArgs e)
        {
            dgv_chitiet.Rows[e.RowIndex].Cells["Column7"].Value = e.RowIndex + 1;
        }
        int dong = -1;
        private void dgv_xeploai_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            if (checkBox1.Checked)
            {
                if (dong != e.RowIndex)
                {
                    int mahs = int.Parse(dgv_xeploai.Rows[e.RowIndex].Cells[3].Value.ToString());
                    load_mon(mahs);
                    dong = e.RowIndex;
                }
            }
        }

        private void frmTongKet_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
            {
                if (this.ActiveControl.Name != "dataGridView")
                {
                    this.SelectNextControl(this.ActiveControl, true, true, true, false);
                }
            }

            else if (e.KeyCode == Keys.Escape)
            {
                btn_thoat_Click(sender, null);
            }
        }

        private void btn_thoat_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private bool testData()
        {
            for (int i = 0; i < dgv_xeploai.Rows.Count; i++)
            {
                if (dgv_xeploai.Rows[i].Cells[6].Value.ToString() == "" || dgv_xeploai.Rows[i].Cells[7].Value.ToString() == "")
                {
                    return false;
                }
            }
            return true;
        }

        private void btn_ghi_Click(object sender, EventArgs e)
        {
            try
            {
                if (!testData())
                {
                    MessageBox.Show("Bạn phải nhập học lực hay hạnh kiểm của học sinh !", "Thông báo");
                    return;
                }
                DiemHKDAO.delete(int.Parse(cbb_hocky.SelectedValue.ToString()), int.Parse(cbb_lop.SelectedValue.ToString()));
                for (int i = 0; i < dgv_xeploai.Rows.Count; i++)
                {
                    DiemHKDTO d = new DiemHKDTO();
                    d.Id_hk = int.Parse(cbb_hocky.SelectedValue.ToString());
                    d.Id_lop = int.Parse(cbb_lop.SelectedValue.ToString());
                    d.Id_hs = int.Parse(dgv_xeploai.Rows[i].Cells[1].Value.ToString());
                   
                    if (dgv_xeploai.Rows[i].Cells[3].Value.ToString() != "")
                    {
                        d.Tbhk1 = float.Parse(dgv_xeploai.Rows[i].Cells[3].Value.ToString());
                    }

                    if (dgv_xeploai.Rows[i].Cells[4].Value.ToString() != "")
                    {   
                        d.Tbhk2 = float.Parse(dgv_xeploai.Rows[i].Cells[4].Value.ToString());
                    }

                    if (dgv_xeploai.Rows[i].Cells[5].Value.ToString() != "")
                    {
                        d.Tbhk = float.Parse(dgv_xeploai.Rows[i].Cells[5].Value.ToString());
                    }
                    
                    d.Hoc_luc = int.Parse(dgv_xeploai.Rows[i].Cells[6].Value.ToString());
                    d.Hanh_kiem = int.Parse(dgv_xeploai.Rows[i].Cells[7].Value.ToString());
                    DiemHKDAO.Insert(d);
                }
                MessageBox.Show("Thực hiện ghi dữ liệu thành công.", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
                cbb_lop.Focus();
            }
            catch (Exception ex)
            {
                MessageBox.Show(" Loi ghi du lieu " + ex.Message, "Error");
            }
        }

        private void btnFilter_Click(object sender, EventArgs e)
        {
            if (cbb_hocky.Text == "")
            {
                MessageBox.Show("Bạn phải chọn học kỳ !", "Thông báo");
                cbb_hocky.Focus();
                return;
            }
            if (cbb_lop.Text == "")
            {
                MessageBox.Show("Bạn phải chọn lớp !", "Thông báo");
                cbb_lop.Focus();
                return;
            }
            load_xeploai();
            if (checkBox1.Checked)
            {
                if (dgv_xeploai.RowCount > 0)
                {
                    int mahs = int.Parse(dgv_xeploai.Rows[0].Cells[3].Value.ToString());
                    load_mon(mahs);
                }
                else
                {
                    load_mon(-1);
                }
            }
            dong = -1;
        }

        private void dgv_xeploai_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }

        private void dgv_xeploai_DataError(object sender, DataGridViewDataErrorEventArgs e)
        {

        }

       
        /* select id_hl, ten_hl,d_min, d_max, d_khong_che from hoc_luc
select id_h_kiem, ten_h_kiem from hanh_kiem

select id_lop, id_hs, id_hk, tbhk, hoc_luc, hanh_kiem from diem_hk hk, hoc_sinh hs where hk.id_hs = hs.id_hs and hk.id_lop = 
         */

    }
}
